#!/usr/bin/env python
# -*- coding: utf-8 -*-
import time
from datetime import date
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, create_engine, Integer, VARCHAR, DateTime, DATETIME, DATE, Date, text

Computer_Info_Dict = {
    "computer1": "10.0.40.223",
    "computer2": "10.0.40.42",
    "computer3": "10.0.40.194",
    "computer4": "10.0.40.223",
    "computer5": "10.0.40.223",
    "computer6": "10.0.40.42",
}



def make_model(TableName):
    """sqlalchemy 动态创建表对象"""

    Base = declarative_base()  # 创建对象的基类(生成一个SQLORM基类)

    class table_model(Base):
        __tablename__ = TableName

        # 表的结构
        id = Column(Integer, primary_key=True)
        ip = Column(VARCHAR(20))
        cpu = Column(VARCHAR(20))
        # cpu_t = Column(VARCHAR(20))
        disk = Column(VARCHAR(20))
        memory = Column(VARCHAR(20))
        gpu = Column(VARCHAR(20))
        date = Column(DATE)
        event = Column(VARCHAR(20))

    return table_model


class QueryModel():
    """数据表查询对象"""

    def __init__(self, TableObj):
        # 初始化数据库链接
        engine = create_engine('postgresql+psycopg2://postgres:python@10.0.40.223:5432/zzkyd', echo=True)
        # 创建DBSession类型
        DBSession = sessionmaker(bind=engine)
        self.session = DBSession()
        self.TabModel = self.session.query(TableObj)
        self.table_name = TableObj.__tablename__
        self.tab_obj = TableObj
        self.ip = Computer_Info_Dict.get(self.table_name, None)

        # 初始化获取基础硬件信息
        self.CPU_Name = -1
        self.C_Disk_Size = -1
        self.Memory_Size = -1
        self.GPU_Size = -1
        self.cpu_usage
        self.get_memory_info()
        self.get_disk_info()
        self.get_gpu_info()

    @property
    def query_all(self):
        """查询所有数据"""
        return self.TabModel.filter().all()

    @property
    def query_last(self):
        """查询最后一条数据"""
        # [(285, '10.0.40.42', 'Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz,2', '100.0,73.4,1155.1,186.1,16.1', '16,11,31.8', '6144.0,686.8,11.2', datetime.datetime(2020, 7, 24, 10, 26, 9, 179375), None)]

        # TODO 方法一，使用原生SQL语句
        # sql = """select * from {} order by id desc limit 1;""".format(self.table_name)
        # return self.session.execute(text(sql)).fetchall()

        # TODO 方法二，使用ORM对象关系映射
        result = self.TabModel.order_by(self.tab_obj.date.desc()).first()  # 按日期查询取最后一条数据
        if result:
            return result
        else:
            return None

    @property
    def cpu_usage(self):
        """实时获取cpu使用率，初始化调用此函数可获得self.CPU_Name信息"""
        last_data_obj = self.query_last
        if not last_data_obj:
            return 0

        CPU_INFO_str = last_data_obj.cpu  # Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz,2
        CPU_INFO_li = CPU_INFO_str.split(",")  # ['Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz', '2']
        self.CPU_Name = CPU_INFO_li[0]
        CPU_Usage = int(CPU_INFO_li[1])  # 2  Int 型
        return CPU_Usage

    def get_disk_info(self, type=None):
        """获取硬盘信息"""
        last_data_obj = self.query_last
        if not last_data_obj:
            return 0

        C_Disk_INFO_str = last_data_obj.disk  # 100.0,73.4,1155.1,186.1,16.1
        C_Disk_INFO_li = C_Disk_INFO_str.split(",")  # ['100.0', '73.4', '1155.1', '186.1', '16.1']
        self.C_Disk_Size = C_Disk_INFO_li[0]  # 100.0  系统盘总容量
        C_Usage = float(C_Disk_INFO_li[1])  # 系统盘使用率
        total_Size = float(C_Disk_INFO_li[2])  # 硬盘总容量
        total_Used = float(C_Disk_INFO_li[3])  # 硬盘已使用
        total_Usage = float(C_Disk_INFO_li[4])  # 硬盘使用率

        if type == "C_Size":
            return self.C_Disk_Size
        elif type == "C_Usage":
            return C_Usage
        elif type == "total_Size":
            return total_Size
        elif type == "total_Used":
            return total_Used
        elif type == "total_Usage":
            return total_Usage

    def get_memory_info(self, type=None):
        """获取内存信息"""
        last_data_obj = self.query_last
        if not last_data_obj:
            return 0

        memory_INFO_str = last_data_obj.memory
        memory_INFO_li = memory_INFO_str.split(",")
        self.Memory_Size = float(memory_INFO_li[0])  # 内存总量
        m_Can_Use = float(memory_INFO_li[1])  # 内存可用量
        m_Usage = float(memory_INFO_li[2])  # 内存使用率

        if type == "m_Size":
            return self.Memory_Size
        elif type == "m_Can_Use":
            return m_Can_Use
        elif type == "m_Usage":
            return m_Usage

    def get_gpu_info(self, type=None):
        """获取GPU信息"""
        last_data_obj = self.query_last
        if not last_data_obj:
            return 0

        gpu_INFO_str = last_data_obj.gpu
        gpu_INFO_li = gpu_INFO_str.split(",")
        self.GPU_Size = float(gpu_INFO_li[0])  # GPU总量
        gpu_Used = float(gpu_INFO_li[1])  # GPU已使用
        gpu_Usage = float(gpu_INFO_li[2])  # GPU使用率

        if type == "gpu_Size":
            return self.GPU_Size
        elif type == "gpu_Used":
            return gpu_Used
        elif type == "gpu_Usage":
            return gpu_Usage

    def query_some(self, num=100):
        """查询固定数量的条目信息，默认查询最近100条数据"""
        # TODO 注意，这里是按日期倒序排列的
        result = self.TabModel.order_by(self.tab_obj.date.desc()).limit(num).all()
        return result

    def query_with_date(self):
        """根据日期查询 TODO 后期根据按日期查询需求决定"""
        timestring = "2020-07-24 16:14:06.132375"
        # timestring = "2020-07-24 0:0:0"

        # TODO 方法一，使用原生SQL语句
        # sql = """select * from {} where date > \'{}\';""".format(self.table_name, timestring)
        # return self.session.execute(text(sql)).fetchall()

        # TODO 方法二，使用ORM对象关系映射
        result = self.TabModel.filter(self.tab_obj.date > """\'{}\'""".format(timestring)).all()
        return result

    @property
    def test(self):
        # sql = """select * from {};""".format(self.table_name)
        # result = self.session.execute(text(sql)).fetchall()

        # self.TabModel.order_by(self.tab_obj.date.desc()).first()  # TODO 按日期获取最后一条数据
        result = self.TabModel.order_by(self.tab_obj.date.desc()).first()
        return result


model1 = QueryModel(make_model("computer1"))
model2 = QueryModel(make_model("computer2"))
model3 = QueryModel(make_model("computer3"))
model4 = QueryModel(make_model("computer4"))
model5 = QueryModel(make_model("computer5"))
model6 = QueryModel(make_model("computer6"))

if __name__ == '__main__':
    # # 初始化数据库链接
    # engine = create_engine('postgresql+psycopg2://postgres:python@10.0.40.223:5432/zzkyd', echo=True)
    # # 创建DBSession类型
    # DBSession = sessionmaker(bind=engine)
    # session = DBSession()
    # Computer = make_model("computer2")
    # comp = session.query(Computer).filter().all()
    #
    # for i in comp:
    #     print("id", i.id)
    #     print("ip", i.ip)
    #     # print("cpu", type(i.cpu))
    #     print("cpu", i.cpu)
    #     print("cpu_t", i.cpu_t)
    #     print("disk", i.disk)
    #     print("memory", i.memory)
    #     print("date", i.date)
    #     # print("date", i.date.today().year)
    #     print("event", i.event)
    #
    #     print('-' * 100 + '\n')
    table1 = make_model("computer2")
    m = QueryModel(table1)
    # m.query_some()
    m.query_with_date()
